# 检测模块 - 包含所有数据检测功能
from config import load_env_structure_setl, load_env_structure_mdtrt, load_env_tbl_name
from sqlalchemy import text
from datetime import datetime

# 加载环境变量
(
    SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME
) = load_env_tbl_name()
(
    SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, 
    SETL_SETL_TIME, SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG
) = load_env_structure_setl()
(
    MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME, MDTRT_HSP_LV, 
    MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE, MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, 
    MDTRT_AGE, MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME, MDTRT_IN_DEPT, MDTRT_OUT_DEPT, 
    MDTRT_VALIDFLAG, MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK, MDTRT_OUTP_DX_CHECK
) = load_env_structure_mdtrt()

def format_dt(dt):
    if isinstance(dt, str):
        return dt
    if dt is None:
        return ""
    if hasattr(dt, 'strftime'):
        return dt.strftime('%Y-%m-%d %H:%M:%S')
    return str(dt)

def inspect_b01_duplicate_setl_ids(engine, check_results, setl_count):
    """B01: 测试结算表的SETL_ID是否有重复"""
    print('============ B01：测试 SETL_ID 是否有重复 ============')

    sql = f"""
    SELECT {SETL_SETL_ID}, COUNT(*) AS count
    FROM {SETL_TBL_NAME}
    GROUP BY {SETL_SETL_ID}
    HAVING COUNT(*) > 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        duplicate_ids = result.fetchall()

    # if duplicate_ids:
    #     print("重复的SETL_ID:")
    #     for row in duplicate_ids[:20]:
    #         print(f"{row[0]} 出现次数: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    WITH T0 AS (
        SELECT {SETL_SETL_ID}, COUNT(*) AS count
        FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_VALIDFLAG} = '1'
        GROUP BY {SETL_SETL_ID}
        HAVING COUNT(*) > 1
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'B01' AS INSPECT_ID,
        '结算表的SETL_ID重复' AS INSPECT_NAME,
        1 AS LV,
        T0.{SETL_SETL_ID} || ', 数量: ' || T0.count AS INFO
    FROM {SETL_TBL_NAME} s
    JOIN T0 ON s.{SETL_SETL_ID} = T0.{SETL_SETL_ID}
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B01，结算表的SETL_ID重复: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B01",
        "inspect_name": "测试结算表的SETL_ID是否有重复",
        "sql": sql,
        "msg": "重复的SETL_ID",
        "info": [{"SETL_ID": row[0], "出现次数": row[1]} for row in duplicate_ids[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ B01：测试 SETL_ID 是否有重复 FINISHED ============')

def inspect_b02_duplicate_mdtrt_ids(engine, check_results, setl_count):
    """B02: 测试结算表的MDTRT_ID是否有重复"""
    print('============ B02：测试 MDTRT_ID 是否有重复 ============')

    sql = f"""
    SELECT {MDTRT_MDTRT_ID}, COUNT(*) AS count
    FROM {MDTRT_TBL_NAME}
    WHERE {MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_MDTRT_ID}
    HAVING COUNT(*) > 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        duplicate_ids = result.fetchall()

    # if duplicate_ids:
    #     print("重复的MDTRT_ID:")
    #     for row in duplicate_ids[:20]:
    #         print(f"{row[0]} 出现次数: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    WITH T0 AS (
        SELECT {SETL_MDTRT_ID}, COUNT(DISTINCT {SETL_SETL_ID}) AS count
        FROM {SETL_TBL_NAME}
        WHERE {SETL_VALIDFLAG} = '1'
        GROUP BY {SETL_MDTRT_ID}
        HAVING COUNT(*) > 1
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS MDTRT_ID,
        'B02' AS INSPECT_ID,
        '结算表的MDTRT_ID重复' AS INSPECT_NAME,
        1 AS LV,
        T0.{MDTRT_MDTRT_ID} || ', 数量: ' || T0.count AS INFO
    FROM {SETL_TBL_NAME} s
    JOIN T0 ON s.{MDTRT_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B02，结算表的一个MDTRT_ID有多个SETL_ID: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B02",
        "inspect_name": "测试就诊表的MDTRT_ID是否有重复",
        "sql": sql,
        "msg": "重复的MDTRT_ID",
        "info": [{"MDTRT_ID": row[0], "出现次数": row[1]} for row in duplicate_ids[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ B02：测试 MDTRT_ID 是否有重复 FINISHED ============')

def inspect_b03_setl_id_not_in_mdtrt(engine, check_results, setl_count):
    """B03: 测试结算表的MDTRT_ID是否在就诊表中存在"""
    print('============ B03：测试 SETL_ID 是否在就诊表中存在 ============')

    sql = f"""
    SELECT s.{SETL_MDTRT_ID}
    FROM {SETL_TBL_NAME} s
    WHERE NOT EXISTS(
        SELECT 1
        FROM {MDTRT_TBL_NAME} md
        WHERE md.{MDTRT_MDTRT_ID} = s.{SETL_MDTRT_ID}
    )
    AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_setl_ids = result.fetchmany(20)

    # if invalid_setl_ids:
    #     print("不存在于就诊表的SETL_ID:")
    #     for row in invalid_setl_ids[:20]:
    #         print(row[0])

    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'B03' AS INSPECT_ID,
        '结算表的MDTRT_ID不存在于就诊表' AS INSPECT_NAME,
        3 AS LV,
        s.{SETL_MDTRT_ID} AS INFO
    FROM {SETL_TBL_NAME} s
    WHERE NOT EXISTS(
        SELECT 1
        FROM {MDTRT_TBL_NAME} md
        WHERE md.{MDTRT_MDTRT_ID} = s.{SETL_MDTRT_ID}
        AND md.{MDTRT_VALIDFLAG} = '1'
    )
    AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B03，结算表的MDTRT_ID不存在于就诊表: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B03",
        "inspect_name": "测试结算表的MDTRT_ID是否在就诊表中存在",
        "sql": sql,
        "msg": "不存在于就诊表的MDTRT_ID",
        "info": [{"MDTRT_ID": row[0]} for row in invalid_setl_ids[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ B03：测试 SETL_ID 是否在就诊表中存在 FINISHED ============')

# 检测结算表中，与就诊表关联（用MDTRT_ID），结算时间早于出院时间
def inspect_b04_setl_time_consistency(engine, check_results, setl_count):
    """B04: 测试结算表的SETL_ID与就诊表的MDTRT_ID时间一致性"""
    print('============ B04：测试 SETL_ID 与 MDTRT_ID 时间一致性 ============')
    
    # 如果SETL_SETL_TIME或MDTRT_OUT_TIME不存在，则跳过检测
    if not SETL_SETL_TIME or not MDTRT_OUT_TIME:
        print("SETL_SETL_TIME或MDTRT_OUT_TIME未定义，跳过B04检测")
        return


    sql = f"""
    SELECT s.{SETL_MDTRT_ID}, s.{SETL_SETL_TIME}, m.{MDTRT_OUT_TIME}
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE s.{SETL_SETL_TIME} < m.{MDTRT_OUT_TIME}
        and trunc(s.{SETL_SETL_TIME}) < trunc(m.{MDTRT_OUT_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        inconsistent_records = result.fetchmany(20)

    # if inconsistent_records:
    #     print("不一致的记录:")
    #     for row in inconsistent_records:
    #         print(f"SETL_ID: {row[0]}, SETL_TIME: {row[1]}, OUT_TIME: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'B04' AS INSPECT_ID,
        '结算表的SETL_TIME与就诊表的OUT_TIME时间不一致' AS INSPECT_NAME,
        2 AS LV,
        'SETL_TIME: ' || s.{SETL_SETL_TIME} || ', OUT_TIME: ' || m.{MDTRT_OUT_TIME} AS INFO
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE s.{SETL_SETL_TIME} < m.{MDTRT_OUT_TIME}
        and trunc(s.{SETL_SETL_TIME}) < trunc(m.{MDTRT_OUT_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B04，结算表的SETL_TIME与就诊表的OUT_TIME时间不一致: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B04",
        "inspect_name": "测试结算表的SETL_TIME与就诊表的OUT_TIME时间一致性",
        "sql": sql,
        "msg": "SETL_TIME与OUT_TIME时间不一致",
        # info中的 SETL_TIME, OUT_TIME 要转变为字符串格式，yyyy-mm-dd hh24:mi:ss
        "info": [{"SETL_ID": row[0], "SETL_TIME": format_dt(row[1]), "OUT_TIME": format_dt(row[2])} for row in inconsistent_records[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ B04：测试 SETL_ID 与 MDTRT_ID 时间一致性 FINISHED ============')

# B05，结算表的IN_DATE_CHECK与就诊表的IN_TIME对应的那一天的一致性
def inspect_b05_setl_in_date_check(engine, check_results, setl_count):
    """B05: 测试结算表的IN_DATE_CHECK与就诊表的IN_TIME一致性"""
    print('============ B05：测试 SETL_IN_DATE_CHECK 与 MDTRT_IN_TIME 一致性 ============')
    
    # 如果SETL_IN_DATE_CHECK或MDTRT_IN_TIME不存在，则跳过检测
    if not SETL_IN_DATE_CHECK or not MDTRT_IN_TIME:
        print("SETL_IN_DATE_CHECK或MDTRT_IN_TIME未定义，跳过B05检测")
        return

    sql = f"""
    SELECT s.{SETL_IN_DATE_CHECK}, m.{MDTRT_IN_TIME}
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE TRUNC(s.{SETL_IN_DATE_CHECK}) != TRUNC(m.{MDTRT_IN_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        inconsistent_records = result.fetchmany(20)

    # if inconsistent_records:
    #     print("不一致的记录:")
    #     for row in inconsistent_records:
    #         print(f"SETL_IN_DATE_CHECK: {row[0]}, MDTRT_IN_TIME: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    SELECT DISTINCT s.{SETL_SETL_ID}, 'B05', '测试结算表的IN_DATE_CHECK与就诊表的IN_TIME不一致', 1,
           'IN_DATE_CHECK: ' || s.{SETL_IN_DATE_CHECK} || ', IN_TIME: ' || m.{MDTRT_IN_TIME}
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE TRUNC(s.{SETL_IN_DATE_CHECK}) != TRUNC(m.{MDTRT_IN_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B05，结算表的IN_DATE_CHECK与就诊表的IN_TIME时间不一致: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B05",
        "inspect_name": "测试结算表的IN_DATE_CHECK与就诊表的IN_TIME一致性",
        "sql": sql,
        "msg": "SETL_IN_DATE_CHECK与MDTRT_IN_TIME不一致",
        "info": [{"SETL_IN_DATE_CHECK": format_dt(row[0]), "MDTRT_IN_TIME": format_dt(row[1])} for row in inconsistent_records[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

# B06，结算表的OUT_DATE_CHECK与就诊表的OUT_TIME对应的那一天的一致性
def inspect_b06_setl_out_date_check(engine, check_results, setl_count):
    """B06: 测试结算表的OUT_DATE_CHECK与就诊表的OUT_TIME一致性"""
    print('============ B06：测试 SETL_OUT_DATE_CHECK 与 MDTRT_OUT_TIME 一致性 ============')
    
    # 如果SETL_OUT_DATE_CHECK或MDTRT_OUT_TIME不存在，则跳过检测
    if not SETL_OUT_DATE_CHECK or not MDTRT_OUT_TIME:
        print("SETL_OUT_DATE_CHECK或MDTRT_OUT_TIME未定义，跳过B06检测")
        return

    sql = f"""
    SELECT s.{SETL_OUT_DATE_CHECK}, m.{MDTRT_OUT_TIME}
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE TRUNC(s.{SETL_OUT_DATE_CHECK}) != TRUNC(m.{MDTRT_OUT_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        inconsistent_records = result.fetchmany(20)

    # if inconsistent_records:
    #     print("不一致的记录:")
    #     for row in inconsistent_records:
    #         print(f"SETL_OUT_DATE_CHECK: {row[0]}, MDTRT_OUT_TIME: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    SELECT DISTINCT s.{SETL_SETL_ID}, 'B06', '测试结算表的OUT_DATE_CHECK与就诊表的OUT_TIME不一致', 1,
           'OUT_DATE_CHECK: ' || s.{SETL_OUT_DATE_CHECK} || ', OUT_TIME: ' || m.{MDTRT_OUT_TIME}
    FROM {SETL_TBL_NAME} s
    JOIN {MDTRT_TBL_NAME} m ON s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    WHERE TRUNC(s.{SETL_OUT_DATE_CHECK}) != TRUNC(m.{MDTRT_OUT_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
    """
    
    with engine.connect() as connection:
        connection.execute(text(sqlIns))
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B06，结算表的OUT_DATE_CHECK与就诊表的OUT_TIME时间不一致: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B06",
        "inspect_name": "测试结算表的OUT_DATE_CHECK与就诊表的OUT_TIME一致性",
        "sql": sql,
        "msg": "SETL_OUT_DATE_CHECK与MDTRT_OUT_TIME不一致",
        # info中的 SETL_OUT_DATE_CHECK, MDTRT_OUT_TIME 要转变为字符串格式，yyyy-mm-dd hh24:mi:ss
        "info": [{"SETL_OUT_DATE_CHECK": format_dt(row[0]), "MDTRT_OUT_TIME": format_dt(row[1])} for row in inconsistent_records[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

# B07，结算表的SETL_TIME早于结算表的OUT_DATE_CHECK
def inspect_b07_setl_time_out_date_check(engine, check_results, setl_count):
    """B07: 测试结算表的SETL_TIME早于OUT_DATE_CHECK"""
    print('============ B07：测试 SETL_TIME 早于 OUT_DATE_CHECK ============')
    
    # 如果SETL_SETL_TIME或SETL_OUT_DATE_CHECK不存在，则跳过检测
    if not SETL_SETL_TIME or not SETL_OUT_DATE_CHECK:
        print("SETL_SETL_TIME或SETL_OUT_DATE_CHECK未定义，跳过B07检测")
        return

    sql = f"""
    SELECT s.{SETL_SETL_ID}, s.{SETL_SETL_TIME}, s.{SETL_OUT_DATE_CHECK}
    FROM {SETL_TBL_NAME} s
    WHERE s.{SETL_SETL_TIME} < s.{SETL_OUT_DATE_CHECK}
        and trunc(s.{SETL_SETL_TIME}) < trunc(s.{SETL_OUT_DATE_CHECK})
        AND s.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        inconsistent_records = result.fetchmany(20)

    # if inconsistent_records:
    #     print("不一致的记录:")
    #     for row in inconsistent_records:
    #         print(f"SETL_ID: {row[0]}, SETL_TIME: {row[1]}, OUT_DATE_CHECK: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    SELECT DISTINCT s.{SETL_SETL_ID}, 'B07', '结算表的SETL_TIME早于OUT_DATE_CHECK', 1,
           'SETL_TIME: ' || s.{SETL_SETL_TIME} || ', OUT_DATE_CHECK: ' || s.{SETL_OUT_DATE_CHECK}
    FROM {SETL_TBL_NAME} s
    WHERE s.{SETL_SETL_TIME} < s.{SETL_OUT_DATE_CHECK}
        and trunc(s.{SETL_SETL_TIME}) < trunc(s.{SETL_OUT_DATE_CHECK})
        AND s.{SETL_VALIDFLAG} = '1'
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"B07，结算表的SETL_TIME早于OUT_DATE_CHECK: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "B07",
        "inspect_name": "测试结算表的SETL_TIME早于OUT_DATE_CHECK",
        "sql": sql,
        "msg": "SETL_TIME早于OUT_DATE_CHECK",
        # info中的 SETL_TIME, OUT_DATE_CHECK 要转变为字符串格式，yyyy-mm-dd hh24:mi:ss
        "info": [{"SETL_ID": row[0], "SETL_TIME": format_dt(row[1]), "OUT_DATE_CHECK": format_dt(row[2])} for row in inconsistent_records[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })